package com.mmy.dao.sys;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.sys.VSysUser;

/**
 * 类的介绍：dao层
 *
 * @author
 * @date 创建日期
 * @version 1.0
 */

public interface VSysUserRepository extends JpaRepository<VSysUser, Integer>, JpaSpecificationExecutor<VSysUser> {
	/**
	 * 查找本公司需要审核的员工
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、SysUserController中toReviewInfo()函数
	 */
	@Query(value = "select * from vSysUser where cMemo='未审核！' and iOrgID=?1", nativeQuery = true)
	List<VSysUser> findByMemoOrgID(int iOrgID);

	/**
	 * 查找用户
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 *       用处：1、在选择审核通过时查询需要修改的用户信息SysUserController中toUpdateReview()函数
	 * 
	 *       后台修改员工
	 */
	@Query(value = "select *  from vSysUser where iUserID=?1", nativeQuery = true)
	VSysUser findByUserID(int iUserID);

	/**
	 * 后台旅行社首页显示 超级管理员登录 显示所有的员工信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex  =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and iUserType!=0 limit :startIndex,:count", nativeQuery = true)
	List<VSysUser> findSearchALL(@Param("strUserName") String strUserName, @Param("strUserSex") String strUserSex,
			@Param("strUserMobile") String strUserMobile, @Param("startIndex") int startIndex,
			@Param("count") int count);

	/**
	 * 后台旅行社首页显示 非超级管理员登录
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex  =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) "
			+ "and (iOrgID=:iOrgID) and iUserType!=0 and bBlack =0 limit :startIndex,:count", nativeQuery = true)
	List<VSysUser> findSearchByOrgID(@Param("iOrgID") int iOrgID, @Param("strUserName") String strUserName,
			@Param("strUserSex") String strUserSex, @Param("strUserMobile") String strUserMobile,
			@Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台旅行社首页显示 超级管理员登录 分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select count(*)  from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex  =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and iUserType!=0", nativeQuery = true)
	int countALL(@Param("strUserName") String strUserName, @Param("strUserSex") String strUserSex,
			@Param("strUserMobile") String strUserMobile);

	/**
	 * 后台旅行社首页显示 非超级管理员登录 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vSysUser where  (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex  =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and (iOrgID=:iOrgID) and iUserType!=0 and bBlack =0 ", nativeQuery = true)
	int countByOrgID(@Param("iOrgID") int iOrgID, @Param("strUserName") String strUserName,
			@Param("strUserSex") String strUserSex, @Param("strUserMobile") String strUserMobile);

	/**
	 * 后台旅行社首页显示 超级管理员登录 显示所有的会员信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex  =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and iUserType=0 limit :startIndex,:count", nativeQuery = true)
	List<VSysUser> findMemberSearchALL(@Param("strUserName") String strUserName, @Param("strUserSex") String strUserSex,
			@Param("strUserMobile") String strUserMobile, @Param("startIndex") int startIndex,
			@Param("count") int count);

	/**
	 * 后台旅行社首页显示 非超级管理员登录
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) "
			+ "and (iOrgID=:iOrgID) and iUserType=0 and bBlack = 0  limit :startIndex,:count", nativeQuery = true)
	List<VSysUser> findMemberSearchByOrgID(@Param("iOrgID") int iOrgID, @Param("strUserName") String strUserName,
			@Param("strUserSex") String strUserSex, @Param("strUserMobile") String strUserMobile,
			@Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台旅行社首页显示 超级管理员登录 分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select count(*)  from vSysUser where (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and iUserType=0", nativeQuery = true)
	int countMemberALL(@Param("strUserName") String strUserName, @Param("strUserSex") String strUserSex,
			@Param("strUserMobile") String strUserMobile);

	/**
	 * 后台旅行社首页显示 非超级管理员登录 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from vSysUser where  (:strUserName = \"\" or cUserName like %:strUserName%) "
			+ "and (:strUserSex = \"\" or bSex =:strUserSex) "
			+ "and (:strUserMobile = \"\" or cUserMobile like %:strUserMobile%) and (iOrgID=:iOrgID) and iUserType=0 and bBlack =0 ", nativeQuery = true)
	int countMemberByOrgID(@Param("iOrgID") int iOrgID, @Param("strUserName") String strUserName,
			@Param("strUserSex") String strUserSex, @Param("strUserMobile") String strUserMobile);

	/**
	 * 查找该公司ID是否有员工
	 * 
	 * @author 创建人 hanxl 整理
	 * @date 创建日期 2020.10.29
	 * 
	 * 
	 */
	@Query(value = "select *  from vSysUser where iOrgID=?1", nativeQuery = true)
	List<VSysUser> getByOrgID(int iOrgID);

}
